<?php
class M_Common extends Model{

    public function M_Common(){
        parent::Model();	
    }

    function mDisplayChildren($parent){
    	$session = $this->session->userdata('us_');
		
		$sQueryUsLevel = $this->db->query("SELECT ll_references.ID as level FROM ll_references WHERE R_CODE = '". $session['us_L'] ."'");
		$rowUsLevel = $sQueryUsLevel->row();
		$usLevel = $rowUsLevel->level;
		
		// retrieve all children of $parent
        $sQuery = " SELECT ID, upc_name, upc_parent, upc_link, upc_target, upc_order";
        $sFrom  = " FROM ll_menu ";
        $sWhere = " WHERE upc_parent  = '". $parent ."'"
                 ." AND user_level = '". $usLevel ."'"
				 ." ORDER BY upc_order;";

		$query = $this->db->query($sQuery.$sFrom.$sWhere);
        
		foreach ($query->result_array() as $row){
			// Response parameters.
			$path["text"]		= html_entity_decode($row["upc_name"]);
			$path["link"]		= html_entity_decode($row["upc_link"]);
			$path["id"]			= $row["ID"];
			$path["position"]	= $row["upc_order"];
			
			$result = $this->db->query("
										SELECT ID, upc_name, upc_parent, upc_link, upc_target, upc_order	
										FROM ll_menu
										WHERE upc_parent  = '". $row["ID"] ."'
										AND user_level = '". $usLevel ."'
										ORDER BY upc_order;
			");
			$sCount = $result->num_rows(); 
			if($sCount > 0){
				$path["leaf"]	= false;
				$path["cls"]	= "folder";
			}else{
				$path["leaf"]	= true;
				$path["cls"]	= "file";
			}
			$nodes[]=$path;
		}
		return $nodes;
    }
	//function to count records number in any table used for data grids
	function mCount($table){	
		$query = $this->db->query("SELECT count(*) as value FROM ".$table);
		return $query->row();
	}
	//---------------------------------------------------------------------------
	/**
	 * 
	 * 
	 * @return json object
	 * @param $table Object	-> tabel
	 * @param $s_limit Object -> start limit
	 * @param $e_limit Object -> end limit
	 * @param $sort Object -> sort field
	 * @param $dir Object -> sort direction
	 * @param $search Object -> search fields
	 * @param $qry Object -> search query
	 * @param $cydep Object -> company dependent table 
	 * @param $where Object -> with default where condition
	 */	
	function mSelectTable($table,$s_limit,$e_limit,$sort,$dir,$search,$qry,$cydep,$where){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];		
		$sQuery			= "SELECT * FROM ". $table;
		$sQuery 		.= " WHERE 1 = 1 ";
		
		//if we have a default search where
		if (isset($where) && !empty($where)){
			$sQuery			.= "AND ". $where." ";
		}
		
		//if company dependent

		if($cydep == 'true'){
			$sQuery .= "AND COMPANY_ID='". $cId ."' ";
		}	

		if($qry && is_array($search) && sizeof($search)) {
			$i = 0;
			$sQuery .= "AND ";
			foreach($search as $value){

				$sQuery .= $value ." LIKE '%".addslashes($qry)."%'";
				$i++;
				if($i != sizeof($search)){
					$sQuery .= " OR ";
				}
			}
		}
		$totalCount = $this->db->query($sQuery)->num_rows();
		$sQuery .= " ORDER BY ". $sort ." ". $dir ." LIMIT ". $s_limit ." , ". $e_limit	;
		
		$query = $this->db->query($sQuery);
		
		$data = array (
						'totalCount'				=> $totalCount,
						'DS_'.strtoupper($table)	=> $query->result_array()
				);
				
		return $data;	
	}
	function mSelectBillTable($s_limit,$e_limit,$sort,$dir,$search,$qry){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];			
		$sQuery = '
					SELECT
						a.ID as ID, a.numar as numar, a.client as client, a.data_emiterii as data_emiterii, a.data_scadentei as data_scadentei, a.moneda as moneda, a.valoare as valoare, b.incasat as incasat
					FROM (
								SELECT
									facturi.ID,
									facturi.numar,
									clienti.nume as client,
									facturi.data_emiterii,
									facturi.data_scadentei,
									facturi.COMPANY_ID,
									ll_references.R_NAME as moneda,
									(sum(articole.valoare) + sum(articole.tva)) AS valoare
								FROM
									facturi
								Inner Join clienti ON facturi.FK_CLIENTI = clienti.ID
								Inner Join articole ON articole.FK_FACTURI = facturi.ID
								Inner Join ll_references ON ll_references.ID = facturi.moneda
								GROUP BY 
									facturi.ID
							) a
					LEFT JOIN
						(
								SELECT 
									incasari.FK_FACTURI,
									IFNULL(sum(incasari.valoarea),"") as incasat
								FROM 
									incasari 
								GROUP BY
									incasari.FK_FACTURI
						) b
					ON 
						a.ID = b.FK_FACTURI';
		$sQuery .= " WHERE a.COMPANY_ID = '". $cId ."'";		
		if($qry && is_array($search) && sizeof($search)) {
			$sQuery .= " AND (";
			$i = 0;
			foreach($search as $value){

				$sQuery .= $value ." LIKE '%".addslashes($qry)."%'";
				$i++;
				if($i != sizeof($search)){
					$sQuery .= " OR ";
				}
			}
			$sQuery.=")";
		}

		$totalCount = $this->db->query($sQuery)->num_rows();					
		$sQuery .= " ORDER BY ". $sort ." ". $dir ." LIMIT ". $s_limit ." , ". $e_limit	;	
		$query = $this->db->query($sQuery);
		$data = array (
						'totalCount'	=> $totalCount,
						'DS_FACTURI'	=> $query->result_array()
				);
		return $data;			
	}
	
   function mSelectReference($cod){
    	
    	$this->db->select('ll_references.ID as value, ll_references.R_NAME as `option`');
    	$this->db->from('ll_references');
    	$this->db->join('ll_families','ll_families.ID = ll_references.FK_FAM');
    	$this->db->where('ll_families.F_CODE =', $cod);
    	$this->db->orderby('ll_references.R_NAME','ASC');
    	
		$query = $this->db->get();
		return $query->result_array();
    }    
    function mSelectReferenceById($rId) {
    	
    	$sQuery = $this->db->query("SELECT R_NAME AS value, R_CODE as code FROM `ll_references` WHERE ID='". $rId ."'");
    	
    	return $sQuery->row();
    }	
    function mSelectUserById($uId) {
    	
    	$sQuery = $this->db->query("SELECT * FROM utilizatori WHERE ID='". $uId ."'");
    	
    	return $sQuery->row();
    }		
   	function mInsert($table,$data){

   		if($this->db->insert($table, $data)){
   			return true;
   		} else {
   			return false;	
   		}	
 	}	
   	function mUpdate($table,$id,$data){
		$this->db->where('ID', $id);
		 
   		if($this->db->update($table, $data)){
   			return true;
   		} else {
   			return false;	
   		}	
 	}		
   	function mDelete($table,$recordID){
		
		$this->db->where('ID', $recordID);
   		if($this->db->delete($table)){
   			return true;
   		} else {
   			return false;	
   		}	
 	}
   	function mEnableDisable($table,$recordID,$action){
		
		$this->db->where('ID', $recordID);
			switch ($action){
				case 'disable':		
					$data = array(
									'activ' => 0
					);
				break;
				case 'enable':
					$data = array(
									'activ' => 1
					);

				break;	
			}	
  		if($this->db->update($table, $data)){
   			return true;
   		} else {
   			return false;	
   		}	
 	}	
	function mSelectWhere($table, $record){
		
		$query = $this->db->get_where($table, array('ID' => $record));
		return $query->result_array();
	}
	function mSelectWhereByCompany($table){
 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
				
		$query = $this->db->get_where($table, array('COMPANY_ID' => $cId));
		return $query->result_array();
	}	
	function mSelect($table,$cyDep,$active){
		if ($cyDep == true){
			$sess 		= $this->session->userdata('us_');
			$cId 		= $sess['us_C'];
			$this->db->where('COMPANY_ID', $cId);
		}
		
		if ($active == true){
			$this->db->where('activ', '1');
		}	
				
		$query = $this->db->get($table);
		return $query->result_array();
	}
	function mSelectProduse($table,$cyDep,$active){
		$this->db->select("ID, CONCAT(nume,' [',um,'] ') as value");

		if ($cyDep == true){
			$sess 		= $this->session->userdata('us_');
			$cId 		= $sess['us_C'];
			$this->db->where('COMPANY_ID', $cId);
		}
		
		if ($active == true){
			$this->db->where('activ', '1');
		}	
		
		$query = $this->db->get($table);
		return $query->result_array();					
	}
    function mSelectCompany($cId){
 		$sQuery = $this->db->get_where('companii', array('ID' => $cId));
    	
		return $sQuery->result_array();    	
    }
	function mSelectTVAPayMode($cId){
    	$this->db->select('ll_references.R_NAME as value');
    	$this->db->from('clienti');
    	$this->db->join('ll_references','ll_references.ID = clienti.platitor_tva');
    	$this->db->where('clienti.ID =', $cId);
		
		$query = $this->db->get();
		return $query->row();		
	}	
    function mSelectBillCode($cId){
    	
 		$sQuery = $this->db->query("SELECT fact_counter as numar,seria FROM counter where COMPANY_ID='". $cId ."'");
    	
		return $sQuery->row();
    } 
	function mInsertBill($bill,$articles,$tva){
		$this->db->trans_start();
			$statusBill		= false;
			$statusArt		= false;
			$statusCounter	= false;		 
			$sess 			= $this->session->userdata('us_');
			$cId 			= $sess['us_C'];		
			$articles 		= json_decode($articles,true);
			$sUpdateCounter = "UPDATE counter SET fact_counter = fact_counter + 1 WHERE COMPANY_ID = '". $cId ."'";
			
			if($this->db->insert('facturi', $bill)){
				$statusBill		= true;
				$last_id	= $this->db->insert_id();
					foreach ($articles as $key => &$value) {
						foreach ($value as $k => $v){
							$value['FK_FACTURI']	= $last_id;
							$value['valoare'] 		= round(($value['cantitate']*$value['pret_unitar']),2);
							$value['tva'] 			= round(($tva/100) * $value['valoare'],2);
						}
						if($this->db->insert('articole', $value)){
							$statusArt = true;
						}
					}	
				 if($this->db->query($sUpdateCounter)){
				 	$statusCounter = true;
				 }
			 }			
		$this->db->trans_complete();
		return array($this->db->trans_status(),$statusBill,$statusArt,$statusCounter); 
	}
	function mSelectBillWhere($ID){
		$bill = array();
		$sQueryBill 	= $this->db->query("SELECT FK_CLIENTI as client, numar, data, data_emiterii, data_scadentei, moneda, nr_auto, delegat, nr_contract, detalii, FK_UTILIZATORI, stampila FROM facturi where ID='". $ID ."'");	
		$sQueryArticles	= $this->db->query("SELECT FK_PRODUSE, cantitate, pret_unitar FROM articole where FK_FACTURI='". $ID ."'");	
		
		$bill = $sQueryBill->result_array();
		$bill[0]['articole'] = json_encode($sQueryArticles->result_array());	
		
		return json_encode($bill[0]);
	}	
	function mInsertPay($data){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
		$statusCounter	= false;
		$statusPay		= false;	
				
		//modalitate = cash	
		if($data['modalitate'] == '49'){
			$this->db->trans_start();
				//selecting counter
				$sQuery = $this->db->query("SELECT chit_counter as value FROM counter where COMPANY_ID='". $cId ."'");
				$sRow = $sQuery->row();
				//adding nr_chit to data array
				$data['nr_chit'] = $sRow->value;
				if($this->db->insert('incasari', $data)){
					$last_id	= $this->db->insert_id();
					$statusPay = true;
					$sUpdateCounter = "UPDATE counter SET chit_counter = chit_counter + 1 WHERE COMPANY_ID = '". $cId ."'";
						 if($this->db->query($sUpdateCounter)){
						 	$statusCounter = true;
						 }
				}
			$this->db->trans_complete();
			return array($this->db->trans_status(),$statusPay,$statusCounter,$last_id); 
		}
		//modalitate = ordin de plata 
		elseif ($data['modalitate'] == '50'){
			$this->db->trans_start();
				$data['data'] = date('Y-m-d');
				if($this->db->insert('incasari', $data)){
					$statusPay = true;
					$sUpdateCounter = "UPDATE counter SET chit_counter = chit_counter + 1 WHERE COMPANY_ID = '". $cId ."'";
						 if($this->db->query($sUpdateCounter)){
						 	$statusCounter = true;
						 }
				}
			$this->db->trans_complete();
			return array($this->db->trans_status(),$statusPay,$statusCounter); 
		}
	}
	function mInsertCanceledBill($data){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
		$rowSeria		= $this->mSelectBillCode($cId);
		$statusPay		= false;
		$statusArticles	= false;
		$statusBill		= false;	
				
		$data['COMPANY_ID'] 	= $cId;
		$data['serie_factura']	= $rowSeria->seria;
		
		$this->db->trans_start();
			if($this->db->insert('facturi_anulate', $data)){
				$this->db->where('FK_FACTURI', $data['FK_FACTURI']);
				
				//deleting any pays
		   		if($this->db->delete('incasari')){
		   			$statusPay = true;
					
					//deleting articles
					$this->db->where('FK_FACTURI', $data['FK_FACTURI']);
					if($this->db->delete('articole')){
				   			$statusArticles = true;
							
							//finally deleting bill
							$this->db->where('ID', $data['FK_FACTURI']);
							if($this->db->delete('facturi')){
								$statusBill = true;
							}	
				   	} 					
		   		} 
			}
		$this->db->trans_complete();
		return array($this->db->trans_status(),$statusPay,$statusArticles,$statusBill); 
	}
	function mInsertCanceledPay($data){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
		
		$statusPay		= false;
				
		$data['COMPANY_ID'] 	= $cId;
		
		$this->db->trans_start();
			if($this->db->insert('incasari_anulate', $data)){
				$this->db->where('ID', $data['OLD_ID']);
				
				//deleting any pays
		   		if($this->db->delete('incasari')){
		   			$statusPay = true;					
		   		} 
			}
		$this->db->trans_complete();
		return array($this->db->trans_status(),$statusPay); 
	}	
	function mSelectNumRows($table,$field,$value){
		$sQuery = "SELECT * FROM ".$table." WHERE ".$field." = '". $value ."'";
		return $this->db->query($sQuery)->num_rows();
	}
	function mSelectClientEmail($bId){
		$sQuery = "SELECT FK_CLIENTI as value FROM facturi WHERE ID = '". $bId ."'";
		$rowFacturi = $this->db->query($sQuery)->row();
		$sQueryClienti = "SELECT email as value FROM clienti WHERE ID = '". $rowFacturi->value ."'";
		$rowClienti = $this->db->query($sQueryClienti)->row();
		return $rowClienti->value;
	}
    function mSelectPayingsForInvoiceXLS($iId){
    	$this->db->select("incasari.ID as ID, incasari.valoarea as valoare, `ll_references`.R_NAME as modalitate, incasari.`data` as data, IF(incasari.nr_chit <> 0, incasari.nr_chit, '') as nr_chit, incasari.nr_ordin as nr_ordin, IF(incasari.data_ordin  <> 0000-00-00, incasari.data_ordin, '') as data_ordin");
    	$this->db->from('incasari');
    	$this->db->join('`ll_references`','`ll_references`.ID = incasari.modalitate');
    	$this->db->join('facturi','incasari.FK_FACTURI = facturi.ID');
    	$this->db->where('facturi.ID', $iId);
    	$this->db->orderby('incasari.`data` DESC');
    	
    	return $this->db->get();      	
    }
	function mSelectTVAJournal($moneda,$ds,$de,$s_limit,$e_limit,$sort,$dir){
		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];		
		
    	$sQuery = "select 
						facturi.ID, facturi.numar as numar, clienti.nume as client, facturi.`data`as data, sum(articole.valoare) as valoare, sum(articole.tva) as tva, sum(articole.valoare) + sum(articole.tva) as total
		 			from 
						facturi
					left join 
						articole on articole.FK_FACTURI = facturi.ID
					left join 
						clienti on facturi.FK_CLIENTI = clienti.ID";
		$sQuery.= " where facturi.COMPANY_ID='".$cId."' and facturi.facturat='1'";
		$sQuery.= " and facturi.moneda='".$moneda."' and facturi.`data` >='".$ds."' and facturi.`data` <='".$de."'";
		$sQuery.= " group by facturi.ID order by ".$sort." ".$dir; 
			$totalCount = $this->db->query($sQuery)->num_rows();
		$sQuery.= " limit ".$s_limit.",".$e_limit;	
		
		$query = $this->db->query($sQuery);
		
		$data = array (
						'totalCount'					=> $totalCount,
						'DS_'.strtoupper("jurnal_tva")	=> $query->result_array()
				);
				
		return $data;		
	}	
    function mSelectHomeBorderou($moneda,$ds,$de,$s_limit,$e_limit,$sort,$dir) {
 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
		   	
    	$sQuery ="	select 
						facturi.numar as numar, incasari.`data` as data, clienti.nume as client, sum(incasari.valoarea) as valoare
					from 
						facturi
					left join 
						incasari on incasari.FK_FACTURI = facturi.ID
					left join 
						clienti on facturi.FK_CLIENTI = clienti.ID";
		$sQuery.= " where facturi.COMPANY_ID='".$cId."'";
		$sQuery.= " and facturi.moneda='".$moneda."' and incasari.`data` >='".$ds."' and incasari.`data` <='".$de."'";
		$sQuery.= " group by facturi.ID order by ".$sort." ".$dir; 
 			$totalCount = $this->db->query($sQuery)->num_rows();
		$sQuery.= " limit ".$s_limit.",".$e_limit;	
		   	
    	$query = $this->db->query($sQuery);
		
		$data = array (
						'totalCount'						=> $totalCount,
						'DS_'.strtoupper("borderou_casa")	=> $query->result_array()
				);
				
		return $data;   
    }  
	function mSelectNotPayedBills($excel,$table,$s_limit,$e_limit,$sort,$dir,$search,$qry){

 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];	
		
		$sQuery = "
		 * from (
			select d.ID, d.numar, d.nume as client, d.data_emiterii, d.data_scadentei, d.R_NAME as moneda, d.COMPANY_ID as compania, d.INV_OVERDUE as overdue, c.facturat, c.incasat, (facturat-incasat) as sold from
			(	
				select a.FK_FACTURI, a.valoare as facturat, IFNULL(b.valoarea,0) as incasat from
				(
					select articole.FK_FACTURI, (sum(articole.valoare) + sum(articole.tva)) AS valoare
					from articole
					group by articole.FK_FACTURI	
				) a left join 
				(
					select incasari.FK_FACTURI, sum(incasari.valoarea) as valoarea
					from incasari
					group by incasari.FK_FACTURI	
				) b on a.FK_FACTURI = b.FK_FACTURI
			) c left join 
			(
			select facturi.ID, facturi.numar, facturi.COMPANY_ID, DATEDIFF(facturi.data_scadentei,CURDATE()) AS INV_OVERDUE, clienti.nume, facturi.data_emiterii, facturi.data_scadentei, ll_references.R_NAME from facturi 
				left join clienti on facturi.FK_CLIENTI = clienti.ID
				left join ll_references on ll_references.ID = facturi.moneda
			) d on c.FK_FACTURI = d.ID
			where incasat < facturat
		) e	
			WHERE compania = '". $cId."'				
		";
		
		if (isset($table) and !empty($table) && ($table == 'facturi_scadente')){
			$sQuery .= " AND overdue <= 0"; 
		}
			
		if($qry && is_array($search) && sizeof($search)) {
			$sQuery .= " AND (";
			$i = 0;
			foreach($search as $value){

				$sQuery .= $value ." LIKE '%".addslashes($qry)."%'";
				$i++;
				if($i != sizeof($search)){
					$sQuery .= " OR ";
				}
			}
			$sQuery .= ")";
		}
	
		if(isset($excel) && !empty($excel) && ($excel == true)){
			$sQuery.= " order by ".$sort." ".$dir; 	
			$sQuery.= " limit ".$s_limit.",".$e_limit;	
						
			$this->db->select($sQuery,false);
			return $this->db->get();			
		}else {
	   		$this->db->select($sQuery,false);
			$partialquery = $this->db->get();			

			$totalCount = $partialquery->num_rows();
		
			$sQuery.= " order by ".$sort." ".$dir; 	
			$sQuery.= " limit ".$s_limit.",".$e_limit;	
									
	   		$this->db->select($sQuery,false);
			$query = $this->db->get();
			
			$data = array (
							'totalCount'							=> $totalCount,
							'DS_'.strtoupper($table)				=> $query->result_array()
			);			
			return $data;							
		}		
		
	} 	
	function mSelectPaysXLS($excel,$table,$s_limit,$e_limit,$sort,$dir,$search,$qry){
 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];		
		
		$sQuery = "data, beneficiar, valoare, numar, IF(modalitate = '49', 'Cash', 'OP') as modalitateplata, explicatie, observatii from plati where COMPANY_ID='". $cId ."'";
		if($qry && is_array($search) && sizeof($search)) {
			$sQuery .= " AND (";
			$i = 0;
			foreach($search as $value){

				$sQuery .= $value ." LIKE '%".addslashes($qry)."%'";
				$i++;
				if($i != sizeof($search)){
					$sQuery .= " OR ";
				}
			}
			$sQuery .= ")";
		}
		$sQuery.= " order by ".$sort." ".$dir; 	
		$sQuery.= " limit ".$s_limit.",".$e_limit;	
		
		$this->db->select($sQuery,false);
		return $this->db->get();			
	}
	
	/**
	 * functii specifice afisarii pe prima pagina
	 * 
	 */ 
	 function mSelectCount($table){
 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];
		
		$query = $this->db->query("SELECT count(*) as value FROM ". $table ." WHERE COMPANY_ID='". $cId ."'");
		return $query->row();
	 }
	 function mSelectHPClienti($tip){
 		$sess 			= $this->session->userdata('us_');
		$cId 			= $sess['us_C'];	 	
	 	
		$sQuery = "SELECT count(*) as value FROM clienti WHERE COMPANY_ID='". $cId ."'";
		if (isset($tip) && !empty($tip)){
			$sQuery .= " AND tip = '". $tip ."'";
		}
		$query = $this->db->query($sQuery);
		return $query->row();
	 }		
}
?>